﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using Grishko.DataTier.SqlLibrary;
using Ecopsy.Tools;
using Grishko.DataTier;
using OutdoorLib.BaseEntity.Constraction;
using OutdoorLib.BaseEntity.Contract;

namespace OutdoorLib.BaseEntity
{
    /// <summary>
    /// Список возвращает xml соответсвующий SideItemBox
    /// </summary>    
    public class SideListWithContracts : SqlStatmentPropertyOf_PagingList
    {
        /// <summary>
        /// Содержит - сторону - связ. контракты - номер поверхности
        /// </summary>
        public class SurfaceItemBox : SideContractItemBox
        {
            public int Number { get; set; }
            
        }

        public class SideContractItemBox : SideLst.SideItemBox 
        {
            public SideContractItemBox() { }
            public SideShedule Shedule { get; set; }
        }



        public class SqlLoadStatment : ListLoadStatment
        {
            /// <summary>
            /// OnlyFull- полностью заполненные поверхности
            /// OnlyFree -- стороны имеющие свободные поверхности 
            /// Overflow_Q-- стороны имеющие загрузку есть очередь на поверхности                         
            /// OnlySelected  - отобранные принадл. редактируемой корзине
            /// </summary>
            public enum Q_Type { OnlyFull, OnlyFree, OverFlow_Q,OnlySelected  }

           

            private DateTime _DtStart = Convert.ToDateTime("01.01.2000");
            public DateTime DtStart  { get{return _DtStart ;} set{_DtStart =value ;} }

            private DateTime _DtFinish = Convert.ToDateTime("01.01.2020");
            public DateTime DtFinish { get { return _DtFinish; } set { _DtFinish = value; } }



            private Q_Type[] _Serch_Q_TypeSet = { };
            /// <summary>
            /// перечень интересующих состояний поверхностей - пустое множество - все поверхности
            /// </summary>
            public Q_Type[] Serch_Q_TypeSet
            {
                get { return _Serch_Q_TypeSet; }
                set { _Serch_Q_TypeSet = value; }
            }


            public override string vPropertyOf_ViewName
            {
                get
                {
                    return "v_srch_PropertyOfSide";
                }
            }

            public override string Source_ViewName
            {
                get
                {
                    return "v_Sides";
                }
            }

            protected override string DO_ResultSet_Sql_4()
            {
                string s = " declare @DtStart datetime  declare @DtEnd datetime  select @DtStart=cast('" + DateTimeTools.GetSqlDate(DtStart) + "' as datetime),@DtEnd=cast('" + DateTimeTools.GetSqlDate(DtFinish) + "' as datetime) ";
                return s+base.DO_ResultSet_Sql_4();
            }
            protected override string Do_PageSet_Sql_5()
            {             
                if (_Serch_Q_TypeSet.Length == 0)
                        return string.Empty;

                string Sql_FreeSides = " delete from @SelectList where id not in  (select SideID from SideShedule(@DtStart, @DtEnd) as res where {0}  )";
                
                string Sql_FreeSides_Where= string.Empty ;
                


                int j=_Serch_Q_TypeSet.Length;
                for (int i=0; i< j; i++) 
                {
                    switch (_Serch_Q_TypeSet[i])
                    { 
                        case Q_Type .OnlyFree :
                            Sql_FreeSides_Where = Sql_FreeSides_Where + " (res.MaxQ<res.SurfaceCount -1) " + (j==1 || i == j - 1 ? string.Empty : " or ");
                            continue;
                        case Q_Type.OnlyFull :
                            Sql_FreeSides_Where = Sql_FreeSides_Where + " (res.MaxQ=res.SurfaceCount -1) " + (j == 1 || i == j - 1 ? string.Empty : " or ");
                            continue;
                        case Q_Type.OverFlow_Q:
                            Sql_FreeSides_Where = Sql_FreeSides_Where + " (res.MaxQ>res.SurfaceCount -1) " + (j == 1 || i == j - 1 ? string.Empty : " or ");
                            continue;
                    }
                }

                return (Sql_FreeSides_Where==string .Empty?string.Empty : string.Format(Sql_FreeSides, Sql_FreeSides_Where));
            }
            protected override string Do_PageSetToXml_Sql_6()
            {

                return "select v_sides.id as SideID,Constraction.id as ConstractionID,	v_sides.Xml.value('(/Side/@Litera)[1]', 'nvarchar(50)') Litera,	Constraction.Xml.value('(/Constraction/@GID)[1]', 'nvarchar(50)') Number into #PAGE from v_sides left join 	v_Link_ConstractionSide link on link.SideId=v_sides.Id	join v_Constraction  Constraction on Constraction.ID=link.ConstractionID join @SelectList as SelectList  on SelectList.ID=v_sides.ID select count(*) from #Page select 	v_sides.xml as Side,	Constraction.xml as Constraction,(select res.ID, res.SideID, res.SurfNumber as SurfNumber,ContractSurface.DtStart,ContractSurface.DtFinish,ContractSurface.ContractId,ContractSurface.ContractTypeID from dbo.SurfaceDistribution(v_sides.id,@DtStart,@DtEnd,default,default,default,default)  as res join v_ContractSurface as ContractSurface on ContractSurface.id=res.id FOR XML RAW, ROOT('List')) as SheduleList	 from 	(select #page.*,  ROW_NUMBER() OVER (ORDER BY #page.Number,#page.Litera) AS row_num  from #page) as Page join v_Constraction Constraction on Constraction.id=Page.ConstractionID 	join v_sides on v_sides.id=Page.SideID where row_num between " + _StartRowIndex.ToString() + " and " + _EndRowIndex.ToString() + " ";
            }

        }


        public override void Load()
        {
            SecuretyContext SC = AppContext.GetSC();
            try
            {
                if (null == _SqlStatment) return;

                //_SqlStatment.PrepareStatment();
                string sql = _SqlStatment.GetStatment();

                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
                cmd.CommandText = sql;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Connection = SC.Connection;
                SC.Connection.Open();

                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                adp.Fill(ds);
                DataTable tbl = ds.Tables[1];

                foreach (DataRow rw in tbl.Rows)
                {
                    Side side = (Side)EntitySingle.GetEntity(rw[0].ToString());
                    ConstractionEntity constr = (ConstractionEntity)EntitySingle.GetEntity(rw[1].ToString());
                    
                    SideShedule shedule = new SideShedule();
                    shedule.Side = side;
                    shedule.dtStart = (_SqlStatment as SideListWithContracts.SqlLoadStatment).DtStart;
                    shedule.dtEnd = (_SqlStatment as SideListWithContracts.SqlLoadStatment).DtFinish;                     

                    if (!string.IsNullOrEmpty(rw[2].ToString()))
                    {                        
                        shedule.ReadXml(rw[2].ToString());

                    }

                    SideContractItemBox i = new SideContractItemBox() { Constraction = constr, Side = side, Shedule =shedule };
                    this.Add(i);
                }

                tbl = ds.Tables[0];
                this.OutputRwCnt = Convert.ToInt16(tbl.Rows[0][0].ToString());
            }
            catch
            {
                return;
            }
            finally
            {
                SC.Connection.Close();
            }
        }


        /// <summary>
        /// Получить список поверхостей  
        /// </summary>
        /// <returns></returns>
        public List <SurfaceItemBox> GetSurfaces()
        {
            List<SideListWithContracts.SurfaceItemBox> res = new List<SurfaceItemBox>();
            
            foreach (SideContractItemBox i in this)
            {
                List<SideListWithContracts.SurfaceItemBox> SideSurf = new List<SideListWithContracts.SurfaceItemBox>();                
                
                for (int j=1;j<i.Side .SurfaceCount +1;j++)
                {                    
                    SideListWithContracts.SurfaceItemBox a = new SideListWithContracts.SurfaceItemBox() 
                    { 
                      Constraction = i.Constraction, 
                      Side = i.Side, Number = j 
                    };
                    SideSurf.Add(a); 
                }

                res.AddRange(SideSurf);
            }
            return res;
        }


    }

    
}
